﻿using aier.dataaccess.model;
using Dapper;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class TaskDal
    {
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 根据姓名查找(queryTaskById)
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public TaskView QueryTaskInfoByTaskId(Guid taskid)
        {
            TaskView taskInfos = null;

            //获取用户信息
            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"select * from AIER_TASK_INFO where taskid='{0}' ", taskid);

                taskInfos = a.QueryFirstOrDefault<TaskView>(whereSql);
            });

            return taskInfos;
        }

        public TaskView QueryTaskInfoByTaskId(Guid taskid, bool result)
        {
            TaskView taskInfos = null;

            //获取用户信息
            dapperHelper.ExecueWithoutTransaction(a =>
            {
                if (result == true)
                {
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    string sql = @" select * from AIER_TASK_INFO as task
                                    left join (select Address1_Telephone1,LeadId from Lead) as lead
                                    on task.new_leadId=lead.LeadId /**where**/ ";
                    var sqlTemplate = sqlBuilder.AddTemplate(sql);
                    if (taskid != Guid.Empty)
                    {
                        sqlBuilder.Where("task.TaskId = @taskid", new { taskid = taskid });
                    }

                    taskInfos = a.QueryFirstOrDefault<TaskView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                }
            });

            return taskInfos;
        }

        /// <summary>
        /// 根据客户id查找
        /// </summary>
        /// <param name="leadId">客户id</param>
        /// <returns></returns>
        public IEnumerable<TaskView> QueryTaskByLeadId(Guid leadId)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<TaskView>>(a =>
            {
                string sql = @" select distinct new_screencallbackid, 
                                       new_lead, leadName, new_account, 
                                       accountName, createdon, new_contactresult, 
                                       new_communicateresult, new_description, new_screen, screenName, new_name, Ownerid, 
                                       DomainName, new_task, new_propaganda  
                                       from dbo.AIER_SCREENCALLBACK_INFO /**where**/ /**orderby**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (leadId != Guid.Empty)
                {
                    sqlBuilder.Where("new_lead = @leadId", new { leadId = leadId });
                }
                sqlBuilder.OrderBy("createdon asc");
                return a.Query<TaskView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据负责人id查找
        /// </summary>
        /// <param name="ownerId"></param>
        /// <returns></returns>
        public IEnumerable<TaskView> QueryTaskByOwnerId(Guid ownerId)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<TaskView>>(a =>
            {

                return a.Query<TaskView>("AIER_P_TASK_INFO", new { ownerId = ownerId, stateCode = 0, sort = 0 }, commandType: CommandType.StoredProcedure);

            });
        }

        /// <summary>
        /// 根据查询条件查询任务信息集合
        /// </summary>
        /// <param name="ownerid">负责人id</param>
        /// <param name="newLeadname">患者姓名</param>
        /// <param name="newMobile">手机号</param>
        /// <param name="newTel">座机</param>
        /// <returns></returns>
        public IEnumerable<TaskView> QueryTaskInfoList(Guid ownerid, string newLeadname, string newMobile, string newTel, int skipNum, int pageSize)
        {
            skipNum = (pageSize * (skipNum - 1));
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<TaskView>>(a =>
            {
                string sql = @"select TaskId, Subject, new_type, StateCode, prioritycode, new_status, ScheduledStart, ScheduledEnd, 
                                OwnerId, DomainName, Description, ActualStart, ActualEnd, new_description, new_screenId, 
                                screenName, new_mobile, new_tel, new_leadId, new_leadName, new_propaganda  from AIER_TASK_INFO /**where**/ /**orderby**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("StateCode = 0");
                sqlBuilder.Where("OwnerId = @ownerid", new { ownerid = ownerid });
                if (!string.IsNullOrWhiteSpace(newLeadname) ||
                   !string.IsNullOrWhiteSpace(newTel)
                   || !string.IsNullOrWhiteSpace(newMobile))
                {
                    StringBuilder orWhere = new StringBuilder();
                    orWhere.Append("(");
                    List<string> orwheres = new List<string>(3);

                    if (!string.IsNullOrWhiteSpace(newLeadname))
                    {
                        orwheres.Add($" charindex('{newLeadname}',new_leadName)>0");
                    }
                    if (!string.IsNullOrWhiteSpace(newTel))
                    {
                        orwheres.Add($" charindex('{newTel}',new_tel)>0 ");
                    }
                    if (!string.IsNullOrWhiteSpace(newMobile))
                    {
                        orwheres.Add($" charindex('{newMobile}',new_mobile)>0 ");
                    }
                    orWhere.Append(string.Join("or", orwheres));
                    orWhere.Append(")");

                    sqlBuilder.Where(orWhere.ToString());
                }

                sqlBuilder.OrderBy("Scheduledstart asc  OFFSET @skipNum ROW FETCH NEXT @pageSize rows only", new { skipNum = skipNum, pageSize = pageSize });
                return a.Query<TaskView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        public int QueryTaskInfoCount(Guid ownerid, string newLeadname, string newMobile, string newTel)
        {
            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                string sql = @"select count(*) from AIER_TASK_INFO /**where**/ /**orderby**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("StateCode = 0");
                sqlBuilder.Where("OwnerId = @ownerid", new { ownerid = ownerid });
                if (!string.IsNullOrWhiteSpace(newLeadname) ||
                   !string.IsNullOrWhiteSpace(newTel)
                   || !string.IsNullOrWhiteSpace(newMobile))
                {
                    StringBuilder orWhere = new StringBuilder();
                    orWhere.Append("(");
                    List<string> orwheres = new List<string>(3);

                    if (!string.IsNullOrWhiteSpace(newLeadname))
                    {
                        orwheres.Add($" charindex('{newLeadname}',new_leadName)>0");
                    }
                    if (!string.IsNullOrWhiteSpace(newTel))
                    {
                        orwheres.Add($" charindex('{newTel}',new_tel)>0 ");
                    }
                    if (!string.IsNullOrWhiteSpace(newMobile))
                    {
                        orwheres.Add($" charindex('{newMobile}',new_mobile)>0 ");
                    }
                    orWhere.Append(string.Join("or", orwheres));

                    orWhere.Append(")");

                    sqlBuilder.Where(orWhere.ToString());
                }

                return a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据客户id分页查找(queryTaskByLeadId(String leadid, int fromIndex,int toIndex))
        /// </summary>
        /// <param name="leadId">客户id</param>
        /// <param name="pageNum">页码</param>
        /// <param name="pageSize">每页的信息条数</param>
        /// <param name="orderField">某个字段和其的排序方式</param>
        /// <returns></returns>
        public IEnumerable<TaskView> QueryTaskByLeadIdPage(Guid leadId, int pageNum, int pageSize, string sort)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<TaskView>>(a =>
            {
                int skipRecords = (pageSize * (pageNum - 1));
                string sql = @"select distinct new_screencallbackid, new_lead, leadName, new_account, accountName, createdon, new_contactresult, 
                                new_communicateresult, new_description, new_screen, screenName, new_name, Ownerid, 
                                DomainName, new_task, new_propaganda  from dbo.AIER_SCREENCALLBACK_INFO /**where**/ /**orderby**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (leadId != Guid.Empty)
                {
                    sqlBuilder.Where("new_lead = @leadId", new { leadId = leadId });
                }

                sqlBuilder.OrderBy($" createdon {sort} OFFSET @skipNum ROW FETCH NEXT @pageSize rows only", new { skipNum = skipRecords, pageSize = pageSize });
                return a.Query<TaskView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }



        /// <summary>
        /// 总计条数
        /// </summary>
        /// <param name="ownerId"></param>
        /// <returns></returns>
        public int QueryTaskByOwnerIdCount(Guid ownerId)
        {
            int total = 0;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"select count(1)
	                                                from 
                                                    (
		                                                select 
		                                                t.activityid TaskId
		                                                from Task t with(nolock)
		                                                left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
		                                                where t.OwnerId='{0}' and t.new_type = 100000000 and t.StateCode = 0 
		                                                union all
		                                                select 
		                                                t.activityid TaskId
		                                                from Task t with(nolock)
		                                                left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
		                                                left join LeadBase l with(nolock) on l.LeadId = p.new_lead
		                                                where t.OwnerId='{0}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
	                                                )r ", ownerId);

                object objValue = a.ExecuteScalar(whereSql);
                if (objValue != null) total = int.Parse(objValue.ToString());
            });

            return total;
        }

        /// <summary>
        /// 根据负责人id分页查找(queryTaskByOwnerId(String ownerid,int fromIndex, int toIndex) )
        /// </summary>
        /// <param name="ownerId"></param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>
        /// <param name="order">desc/asc</param>
        /// <returns></returns>
        public IEnumerable<TaskView> QueryTaskByOwnerIdPage(Guid ownerId, int pageNum, int pageSize, string order)
        {
            //获取用户信息

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<TaskView>>(a =>
            {
                int skipRecords = (pageSize - 1) * (pageNum - 1);

                //string pageSql = $@"with tmp as ( select r.*
                //                                 from (
                //                                  select 
                //                                  t.activityid TaskId,
                //                                  t.Subject,
                //                                  100000000 as new_type,
                //                                  dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
                //                                  null as New_mobile,
                //                                  null as new_tel,
                //                                  t.OwnerId             --负责人ID
                //                                  from Task t with(nolock)
                //                                  left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                //                                  left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
                //                                  where t.OwnerId='{ownerId}' and t.new_type = 100000000 and t.StateCode = 0 

                //                                  union all

                //                                  select 
                //                                  t.activityid TaskId,
                //                                  t.Subject,
                //                                  100000001 as new_type,
                //                                  dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
                //                                  isnull(l.new_phone,l.Address1_Telephone1) as New_mobile,
                //                                  l.telephone2 as new_tel,
                //                                  t.OwnerId             --负责人ID
                //                                  from Task t with(nolock)
                //                                  left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                //                                  left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
                //                                  left join Lead l with(nolock) on l.LeadId = p.new_lead
                //                                  where t.OwnerId='{ownerId}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
                //                                 ) as r 
                //                                )   
                //                        select tmp.* from tmp 
                //                        order by tmp.ScheduledStart {order} OFFSET {skipRecords} ROW FETCH NEXT {pageSize} rows only";
                string pageSql = $@"select * into #temp
                                                 from (
                                                  select 
                                                  t.activityid TaskId,
                                                  t.Subject,
                                                  t.new_type,
												  t.ScheduledStart,
                                                  --dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
                                                  null as New_mobile,
                                                  null as new_tel,
                                                  t.OwnerId             --负责人ID
                                                  from Task t with(nolock)
                                                  --left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                                                  left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
                                                  where t.OwnerId='{ownerId}' and t.new_type = 100000000 and t.StateCode = 0
												  
                                                  union all

                                                  select 
                                                  t.activityid TaskId,
                                                  t.Subject,
                                                  t.new_type,
												  t.ScheduledStart,
                                                  --dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
												  l.new_phone as New_mobile,
                                                  --isnull(l.new_phone,l.Address1_Telephone1) as New_mobile,
                                                  l.telephone2 as new_tel,
                                                  t.OwnerId             --负责人ID
                                                  from Task t with(nolock)
                                                  --left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                                                  left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
                                                  left join Lead l with(nolock) on l.LeadId = p.new_lead
                                                  where t.OwnerId='{ownerId}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
                                                 ) as r 
                                                select  TaskId,Subject,new_type,DATEADD(Hour,8, ScheduledStart) ScheduledStart,New_mobile,new_tel,OwnerId from #temp order by ScheduledStart {order} OFFSET {skipRecords} ROW FETCH NEXT {pageSize} rows only
                                                drop table #temp";
                return a.Query<TaskView>(pageSql);
            });

        }



        /// <summary>
        /// 总计条数；2020/11/4 去掉校园筛查的数据
        /// </summary>
        /// <param name="ownerId"></param>
        /// <returns></returns>
        public int QueryTaskByOwnerIdCount1(Guid ownerId)
        {
            int total = 0;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"select count(1)
	                                                from 
                                                    (
		                                                select 
		                                                t.activityid TaskId
		                                                from Task t with(nolock)
		                                                left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
		                                                where t.OwnerId='{0}' and t.new_type = 100000000 and t.StateCode = 0 
                                                        and sc.new_screentype!=100000001
		                                                union all
		                                                select 
		                                                t.activityid TaskId
		                                                from Task t with(nolock)
		                                                left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
		                                                inner join new_screenBase sc on p.new_screen=sc.new_screenId
                                                        left join LeadBase l with(nolock) on l.LeadId = p.new_lead
		                                                where t.OwnerId='{0}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
                                                        and sc.new_screentype!=100000001
	                                                )r ", ownerId);

                object objValue = a.ExecuteScalar(whereSql);
                if (objValue != null) total = int.Parse(objValue.ToString());
            });

            return total;
        }

        /// <summary>
        /// 根据负责人id分页查找(queryTaskByOwnerId(String ownerid,int fromIndex, int toIndex) )
        /// </summary>
        /// <param name="ownerId"></param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>
        /// <param name="order">desc/asc</param>
        /// <returns></returns>
        public IEnumerable<TaskView> QueryTaskByOwnerIdPage1(Guid ownerId, int pageNum, int pageSize, string order)
        {
            //获取用户信息

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<TaskView>>(a =>
            {
                int skipRecords = (pageSize - 1) * (pageNum - 1);

                string pageSql = $@"select * into #temp
                                                 from (
                                                  select 
                                                  t.activityid TaskId,
                                                  t.Subject,
                                                  t.new_type,
												  t.ScheduledStart,
                                                  --dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
                                                  null as New_mobile,
                                                  null as new_tel,
                                                  t.OwnerId             --负责人ID
                                                  from Task t with(nolock)
                                                  --left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                                                  left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
                                                  where t.OwnerId='{ownerId}' and t.new_type = 100000000 and t.StateCode = 0
												  and sc.new_screentype!=100000001
                                                  union all

                                                  select 
                                                  t.activityid TaskId,
                                                  t.Subject,
                                                  t.new_type,
												  t.ScheduledStart,
                                                  --dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
												  l.new_phone as New_mobile,
                                                  --isnull(l.new_phone,l.Address1_Telephone1) as New_mobile,
                                                  l.telephone2 as new_tel,
                                                  t.OwnerId             --负责人ID
                                                  from Task t with(nolock)
                                                  --left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                                                  left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
                                                  inner join new_screenBase sc on p.new_screen=sc.new_screenId                                                  
                                                  left join Lead l with(nolock) on l.LeadId = p.new_lead
                                                  where t.OwnerId='{ownerId}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
                                                  and sc.new_screentype!=100000001
                                                 ) as r 
                                                select  TaskId,Subject,new_type,DATEADD(Hour,8, ScheduledStart) ScheduledStart,New_mobile,new_tel,OwnerId from #temp order by ScheduledStart {order} OFFSET {skipRecords} ROW FETCH NEXT {pageSize} rows only
                                                drop table #temp";
                return a.Query<TaskView>(pageSql);
            });

        }


        /// <summary>
        /// 根据任务ID获取筛查通知信息
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        public ScreenNoticeView GetScreenNoticeData(Guid taskId, Guid ownerId)
        {
            if (taskId != Guid.Empty && ownerId != Guid.Empty)
            {
                return new DapperHelper().ExecueWithoutTransaction(select =>
                {
                    string strsql = @"select task.ActivityId as taskId,screen.new_screenId as screenId,screen.new_name as screenName,task.subject as taskName,task.scheduledend as taskEndData,case screen.new_screenstate when 0 then '新建' when 1 then '发布' when 2 then '撤销' when 4 then '暂停' when 5 then '进行中' when 3 then '已完成' else '无选择' end as taskStatus,
                                      screen.new_startdate as screenStartData,(community.new_provinceName+community.new_cityName+community.new_countyName+community.new_streetName+community.new_name) as screenAddress,
                                      screen.new_checkinaddress as checkinAddress,screen.new_screenteam as screenTeamId
                                      from task
                                      left join new_screenBase screen
                                      on task.regardingobjectid = screen.new_screenid
                                      left join new_community community on screen.new_community=community.new_communityId
                                      /**where**/";
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                    sqlBuilder.Where("task.ActivityId = @taskId", new { taskId = taskId });
                    sqlBuilder.Where("task.OwnerId = @ownerId", new { ownerId = ownerId });
                    return select.QueryFirst<ScreenNoticeView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                });
            }
            return null;
        }

        /// <summary>
        /// 根据筛查小组Id获取筛查小组信息
        /// </summary>
        /// <param name="screenTeamId"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> GetScreenTeamData(Guid screenTeamId)
        {
            if (screenTeamId != Guid.Empty)
            {
                return new DapperHelper().ExecueWithoutTransaction(select =>
                {
                    string strsql = @"select new_screenteam.new_screenteamId,new_screenteam.new_name as screenTeamName,new_screenteam.OwnerIdName as ScreenTeamLead,sys.FullName as screenGroupMember from new_screenteam
                                        left join new_new_screenteam_systemuser as screenteamSys on new_screenteam.new_screenteamid=screenteamSys.new_screenteamid
                                        left join SystemUserBase as sys on sys.systemuserid=screenteamSys.systemuserid
                                      /**where**/";
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                    sqlBuilder.Where("new_screenteam.new_screenteamid = @screenTeamId", new { screenTeamId = screenTeamId });
                    return select.Query<dynamic>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                });
            }
            return null;
        }
    }
}
